package com.artup.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import com.artup.pojo.Material;

/**
 * 素材
 * @author hapday
 * @date 2017年7月14日 @Time 下午1:57:30
 */
@Repository
public interface MaterialDao {
	
	/**
	 * 添加【素材】
	 * @param MaterialVo
	 * @throws Exception
	 */
	@Insert( { "INSERT INTO bud_picture "
			+ "(db_id, user_db_id, name_src, directory, width, height, format, dpi, thumbnail_dir, small_thumbnail_image_path, upload_dt, client, thumbnail_scale, channel, original_size, thumbnail_size) "
			+ "VALUES(#{id, javaType=String, jdbcType=VARCHAR}, #{passportId, javaType=Integer, jdbcType=INTEGER}, #{originalFileName, javaType=String, jdbcType=VARCHAR}"
			+ ", #{orignalStoragePath, javaType=String, jdbcType=VARCHAR}, #{originalWidth, javaType=Integer, jdbcType=INTEGER}"
			+ ", #{originalHeight, javaType=Integer, jdbcType=INTEGER}, #{expandedName, javaType=String, jdbcType=VARCHAR}, #{dpi, javaType=Integer, jdbcType=INTEGER}"
			+ ", #{thumbnailStoragePath, javaType=String, jdbcType=VARCHAR}, #{smallThumbnailImagePath, javaType=String, jdbcType=VARCHAR}, NOW(), #{clientCode, javaType=String, jdbcType=VARCHAR}, #{zoomScale, javaType=Float, jdbcType=FLOAT}"
			+ ", #{channelCode, javaType=String, jdbcType=VARCHAR}, #{originalSize, javaType=Long, jdbcType=BIGINT}, #{thumbnailSize, javaType=Long, jdbcType=BIGINT})" } )
	void insertMaterial(Material material) throws SQLException;
	
	/**
	 * 根据【ID】查询【素材缩略图路径】
	 * @param id 【ID】
	 * @return 【素材缩略图路径】
	 * @throws SQLException
	 */
	@Select( value = { "SELECT m.thumbnail_dir FROM bud_picture m WHERE m.db_id = #{id, javaType=String, jdbcType=VARCHAR}" } )
	String selectMaterialThumbnailPath(String id) throws SQLException;
	
	/**
	 * 根据【IDs】批量查询【素材图片路径列表】
	 * @param ids
	 * @return
	 * @throws SQLException
	 */
	@Select( value = { "<script>SELECT m.DIRECTORY AS orignalStoragePath, m.thumbnail_dir AS thumbnailStoragePath, m.original_size AS originalSize, m.thumbnail_size AS thumbnailSize"
			+ " FROM bud_picture m"
			+ " WHERE m.db_id IN "
				+ "<foreach collection='ids' item='id' open='(' close=')' separator=','>"
					+ "#{id, javaType=String, jdbcType=VARCHAR}"
				+ "</foreach>"
			+ "</script>" } )
	List<Map<String, Object>> batchSelectMaterialImagePathListByIds(@Param("ids") String ids []) throws SQLException;
	
	/**
	 * 根据【ID】查询【素材】
	 * @param id 【ID】
	 * @return 【素材】
	 * @throws SQLException
	 */
	@Select( value = { "SELECT m.db_id AS id, m.user_db_id AS passportId, m.directory AS orignalStoragePath, m.thumbnail_dir AS thumbnailStoragePath, m.small_thumbnail_image_path AS smallThumbnailImagePath, m.width AS originalWidth, m.height AS originalHeight, m.thumbnail_scale AS zoomScale, m.upload_dt AS uploadTime"
			+ " FROM bud_picture m"
			+ " WHERE m.db_id = #{id, javaType=String, jdbcType=VARCHAR}" } )
	Material selectMaterialById(String id) throws SQLException;
	
	/**
	 * 根据【IDs】批量删除【素材】
	 * @param ids 【IDs】
	 * @throws SQLException
	 */
	@Delete( value = { "<script>DELETE FROM bud_picture"
			+ " WHERE db_id IN"
				+ "<foreach collection='ids' item='id' open='(' close=')' separator=','>"
					+ "#{id, javaType=String, jdbcType=VARCHAR}"
				+ "</foreach>"
			+ "</script>" } )
	void batchDeleteMaterialById(@Param("ids") String [] ids) throws SQLException;
	
	/**
	 * 查询【素材列表】
	 * @param material 【素材】
	 * @return 【素材列表】
	 * @throws SQLException
	 */
	@Select( value = { "<script>"
			+ " SELECT COUNT(1)"
			+ " FROM bud_picture m"
			+ " WHERE (m.CLIENT = 'ios' OR m.CLIENT = 'android')"
				+ "<if test='null != passportId and 0 &lt; passportId'>"
					+ " AND m.user_db_id = #{passportId, javaType=Integer, jdbcType=INTEGER}"
				+ "</if>"
				+ "<if test='null != beginUploadTime and \"\" != beginUploadTime'>"
	      			+ " AND upload_dt &gt;= #{beginUploadTime}"
	      		+ "</if>"
	      		+ "<if test='null != endUploadTime and \"\" != endUploadTime'>"
	      			+ " AND upload_dt &lt;= #{endUploadTime}"
	      		+ "</if>"
			+ " </script>" } )
	long selectMaterialTotalCount(Material material) throws SQLException;

	/**
	 * 查询【素材列表】
	 * @param material 【素材】
	 * @return 【素材列表】
	 * @throws SQLException
	 */
	@Select( value = { "<script>"
			+ " SELECT m.db_id AS id, m.user_db_id AS passportId, m.client AS clientCode, m.name_src AS name, m.directory AS orignalStoragePath, m.WIDTH AS originalWidth, m.HEIGHT AS originalHeight, m.thumbnail_dir AS thumbnailStoragePath, m.small_thumbnail_image_path AS smallThumbnailImagePath, m.upload_dt AS uploadTime"
			+ " FROM bud_picture m"
			+ " WHERE (m.CLIENT = 'ios' OR m.CLIENT = 'android')"
				+ "<if test='null != passportId and 0 &lt; passportId'>"
					+ " AND m.user_db_id = #{passportId, javaType=Integer, jdbcType=INTEGER}"
				+ "</if>"
				+ "<if test='null != beginUploadTime and \"\" != beginUploadTime'>"
	      			+ " AND upload_dt &gt;= #{beginUploadTime}"
	      		+ "</if>"
	      		+ "<if test='null != endUploadTime and \"\" != endUploadTime'>"
	      			+ " AND upload_dt &lt;= #{endUploadTime}"
	      		+ "</if>"
			+ " ORDER BY m.UPLOAD_DT DESC"
			+ " LIMIT #{offset, javaType=Long, jdbcType=BIGINT}, #{pageSize, javaType=Integer, jdbcType=INTEGER}"
			+ " </script>" } )
	List<Material> selectMaterialList(Material material) throws SQLException;
}
